using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Text.RegularExpressions;
using Jasongozy.Alaton;
namespace Jasongozy.Copor.Utility {
    public static class MySqlDBHelper {
        private static void SetDbType(MySqlParameter p, string type, DataTable typeTable) {
            type = type.Replace("\t", " ");
            while (type.IndexOf("  ") > -1) {
                type = type.Replace("  ", " ");
            }
            string typeName = type;
            string typeName2 = typeName;
            List<int> args = new List<int>();
            if (typeName.IndexOf("(") > -1) {
                typeName = typeName.Substring(0, typeName.IndexOf("("));
                typeName2 = type.Substring(type.IndexOf("(") + 1).Replace(")", string.Empty);
                string[] tempTypeName2 = typeName2.Split(new char[] { ' ', ',', '\t' }, StringSplitOptions.RemoveEmptyEntries);
                typeName2 = string.Empty;
                for (int i = 0; i < tempTypeName2.Length; i++) {
                    typeName2 += "{" + i + "}";
                    if (i < tempTypeName2.Length - 1) {
                        typeName2 += ",";
                    }
                    try {
                        args.Add(int.Parse(tempTypeName2[i]));
                    } catch (Exception ex) {
                        ;
                    }
                }
                typeName2 += "(" + typeName2 + ")";
            }
            DataRow[] rows = typeTable.Select("TypeName='" + typeName + "'");
            if (rows.Length != 1) {
                rows = typeTable.Select("CreateFormat='" + typeName2 + "'");
            }
            if (rows.Length == 1) {
                string ProviderDbType = rows[0]["ProviderDbType"].ToString();

                string CreateParameters = string.Empty;
                if (rows[0]["CreateParameters"] != null && rows[0]["CreateParameters"] != DBNull.Value) {
                    CreateParameters = rows[0]["CreateParameters"].ToString();
                }

                p.MySqlDbType = (MySqlDbType)(int.Parse(ProviderDbType));

                if (CreateParameters != string.Empty) {
                    string[] detail = CreateParameters.Split(new char[] { ' ', ',', '\t' }, StringSplitOptions.RemoveEmptyEntries);
                    for (int i = 0; i < detail.Length; i++) {
                        switch (detail[i]) {
                            case "size":
                                p.Size = args[i];
                                break;
                            case "precision":
                                p.Precision = (byte)args[i];
                                break;
                            case "scale":
                                p.Scale = (byte)args[i];
                                break;
                        }
                    }
                }
            } else {
                throw new ArgumentException();
            }
        }
        static Dictionary<string, DataTable> _DataTypes = new Dictionary<string, DataTable>();
        static ParameterDirection GetParameterDirection(string inout) {
            switch (inout) {
                case "in":
                    return ParameterDirection.Input;
                case "out":
                    return ParameterDirection.Output;
                case "inout":
                    return ParameterDirection.InputOutput;
            }
            return ParameterDirection.ReturnValue;
        }

        static readonly Regex _TypeParser = new Regex(@"\s*(in|out|inout)?\s+([a-zA-Z_0-9]+)\s+([a-zA-Z_0-9]+(\s+[a-zA-Z_0-9]+)?(\(\d+(,\s*\d+)?\))?)\s*,?",
                RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Singleline);


        public static string[] GetAllTables(string connectionString, string database) {
            const string sql = "SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS where table_schema = '{0}'";
            List<string> tables = new List<string>();
            ;
            using (MySqlDataReader r = MySqlHelper.ExecuteReader(connectionString, CommandType.Text, sql)) {
                while (r.Read()) {
                    tables.Add(r[0].ToString());
                }
            }
            return tables.ToArray();
        }

        public static DataTable GetAllProcedures(string connectionString, string database) {
            const string sql = "SELECT * FROM mysql.proc WHERE db = '{0}' AND type = 'PROCEDURE'";
            DataTable t = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connectionString)) {
                t = MySqlHelper.ExecuteDataset(conn, CommandType.Text, string.Format(sql, database)).Tables[0];
            }
            return t;
        }

        public static DataTable GetSchemaTable(string connectionString, string database, string table) {
            const string Sql = "select * from {0}.{1} where 0 <> 1";
            using (MySqlConnection conn = new MySqlConnection(connectionString)) {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Connection = conn;
                cmd.CommandText = string.Format(Sql, database, table);
                cmd.CommandTimeout = 300;
                conn.Open();
                using (MySqlDataReader r = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) {
                    return r.GetSchemaTable();
                }
            }
        }

        public static DataTable GetDataTypeTable(string connectionString, string database) {

            using (MySqlConnection conn = new MySqlConnection(connectionString)) {
                conn.Open();
                return conn.GetSchema("DataTypes");
            }
        }

        public static MySqlParameter[] GetStoreProcedureParams(string connectionString, string database, string inputArgs, string returnArgs) {
			if (!_DataTypes.ContainsKey(connectionString)) {
				_DataTypes[connectionString] = GetDataTypeTable(connectionString, database);
			}

			List<MySqlParameter> result = new List<MySqlParameter>();

			if (inputArgs != null && !string.IsNullOrEmpty(inputArgs.Trim())) {
				inputArgs = inputArgs.Trim().Replace("\r", " ").Replace("\n", " ");
				foreach (Match m in _TypeParser.Matches(inputArgs)) {
					MySqlParameter p = new MySqlParameter();
					p.ParameterName = m.Groups[2].Value;
					p.Direction = GetParameterDirection(m.Groups[1].Value.ToLower());
					SetDbType(p, m.Groups[3].Value.ToUpper(), _DataTypes[connectionString]);
					result.Add(p);
				}
			}
			if (returnArgs != null && !string.IsNullOrEmpty(returnArgs.Trim())) {
				MySqlParameter p = new MySqlParameter();
				p.Direction = ParameterDirection.ReturnValue;
				result.Add(p);
			}

			return result.ToArray();
		}


        public static MySqlProcSchema GetSqlProcInfo(string connectionString, string dbName, string procName) {
            DataTable allProcInfos = GetAllProcedures(connectionString, dbName);
            DataRow[] rows = allProcInfos.Select(string.Format("name='{0}'", procName));
            StringBuilder sb = new StringBuilder(500);
           
            MySqlProcSchema mySqlProc = new MySqlProcSchema ();
            mySqlProc.ProcName = procName;
            if(rows.Length > 0){
                DataRow r = rows[0];
                 string paramList = Encoding.UTF8.GetString((byte[])r["param_list"]);
                string returns = Encoding.UTF8.GetString((byte[])r["returns"]);
                MySqlParameter[] newParams = MySqlDBHelper.GetStoreProcedureParams(connectionString, dbName, paramList, returns);
                mySqlProc.Params = newParams;
                return mySqlProc;
            }
            return null;
        }

        public static TableSchema GetTableSchema(string connectionStr, string dbName, string tableName) {
            DataTable dt = MySqlDBHelper.GetSchemaTable(connectionStr, dbName, tableName);
            TableSchema schema = new TableSchema();
            List<string> Pks = new List<string>();
            List<string> NullableColumns = new List<string>();
            List<string> Columns = new List<string>();
            List<string> ColumnTypes = new List<string>();
            List<string> YearColumns = new List<string>();
            foreach (DataRow row in dt.Rows) {
                if (row["ColumnName"].ToString() == "LastUpdate") continue;

                if (Convert.ToBoolean(row["IsKey"].ToString())) {
                    Pks.Add(row["ColumnName"].ToString());
                }
                if (Convert.ToBoolean(row["AllowDBNull"].ToString())) {
                    NullableColumns.Add(row["ColumnName"].ToString());
                }
                Columns.Add(row["ColumnName"].ToString());
                ColumnTypes.Add(row["DataType"].ToString());
                if (dt.Columns.Contains("DefaultValue") && row["DefaultValue"] != null) {
                    schema.DefaultValues.Add(row["ColumnName"].ToString(), row["DefaultValue"].ToString());
                } 
                if (row["ProviderType"].ToString() == ((int)MySqlDbType.Year).ToString()) {
                    YearColumns.Add(row["ColumnName"].ToString());
                }
            }

            schema.Pks = Pks.ToArray();
            schema.NullableColumns = NullableColumns.ToArray();
            schema.Columns = Columns.ToArray();
            schema.ColumnTypes = ColumnTypes.ToArray();
            schema.TableName = tableName;
            schema.YearColumns = YearColumns.ToArray();
            schema.DataGroupName = schema.TableName + "Group";

            return schema;
        }
    }
}
